package by.semerikov.dao.factory.mysqlJdbc;

import java.sql.Connection; 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 


import com.mysql.jdbc.PreparedStatement;

import by.semerikov.dao.IDAO;
import by.semerikov.dao.Person;
import by.semerikov.dao.Proposal;
import by.semerikov.data.Connector;
import by.semerikov.exceptions.BadObjectException;

public class ProposalDAO implements IDAO<Proposal> {

	private Connection con;
	PreparedStatement insertStatement;

	public ProposalDAO() throws SQLException {
		
		con = Connector.getConnection();
	}

	public ProposalDAO(Connection con) {
		super();
		this.con = con;
	}

	@Override
	public void create(Proposal object) throws BadObjectException {
		try {
			PreparedStatement preparedState;
			if (object.getDriver() != null)
				 preparedState = (PreparedStatement) con
					.prepareStatement("insert into transportsystem.proposal (name,description,proposal.from,proposal.to,cost,state,personal_id) values(?,?,?,?,?,?,?)");
			else
				 preparedState = (PreparedStatement) con
				.prepareStatement("insert into transportsystem.proposal (name,description,proposal.from,proposal.to,cost,state) values(?,?,?,?,?,?)");
		
			preparedState.setString(1, object.getName());
			preparedState.setString(2, object.getDescription());
			preparedState.setString(3, object.getFrom());
			preparedState.setString(4, object.getTo());
			preparedState.setString(5, object.getCost());
			preparedState.setString(6, object.getState());
			if (object.getDriver() != null)
				preparedState.setInt(7, object.getDriver().getId());
			
			preparedState.executeUpdate();
		} catch (Exception e) {
			throw new BadObjectException(e);
		}

	}

	@Override
	public List<Proposal> read(Proposal object) throws BadObjectException {
		ArrayList<Proposal> newList = new ArrayList<Proposal>();
		try {
			PreparedStatement preparedState = doReadPreparedStatement(object,
					"SELECT * FROM transportsystem.proposal ");
			ResultSet resultset = preparedState.executeQuery();
			while (resultset.next()) {
				PersonDAO pd = new PersonDAO();
				Person driver = null;
				if(resultset.getString("personal_id")!=null)
				{
					List<Person> listPerson = pd.read(new Person(Integer
						.parseInt(resultset.getString("personal_id")), null,
						null, null, null, null));
				
					if (listPerson != null && listPerson.size() > 0) {
						driver = new Person(listPerson.get(0));
					} else {
						driver = null;
					}
				}
				newList.add(new Proposal(Integer.parseInt(resultset.getString("id"))
						, resultset.getString("name")
						, resultset.getString("description")
						, resultset.getString("from")
						, resultset.getString("to")
						, resultset.getString("cost")
						, resultset.getString("state")
						, driver));
			}
			return newList;
		} catch (Exception e) {
			e.printStackTrace();
			throw new BadObjectException(e);
		}
	}

	private PreparedStatement doReadPreparedStatement(Proposal object,
			String begin) 
		throws SQLException {
			String newStr = begin;
			Map<String, Integer> map = new HashMap<String, Integer>();
			int number = 1;
			if (object.getId() != null) {
				newStr += "where  id = ?";
				map.put("id", number);
				number++;
			}
			if (object.getName() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " name Like ?";
				map.put("name", number);
				number++;
			}
			if (object.getDescription() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " description Like ?";
				map.put("description", number);
				number++;
			}
			if (object.getFrom() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " proposal.from Like ?";
				map.put("from", number);
				number++;
			}
			if (object.getTo() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " proposal.to Like ?";
				map.put("to", number);
				number++;
			}
			if (object.getCost() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " cost Like ?";
				map.put("cost", number);
				number++;
			}
			if (object.getState() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " state = ?";
				map.put("state", number);
				number++;
			}
			if (object.getDriver() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " personal_id = ?";
				map.put("personal_id", number);
				number++;
			}
			PreparedStatement newPS = (PreparedStatement) con
					.prepareStatement(newStr);
			if (map.get("id") != null) {
				newPS.setInt(map.get("id"), object.getId());
			}
			if (map.get("name") != null) {
				newPS.setString(map.get("name"), "%" + object.getName() + "%");
			}
			if (map.get("description") != null) {
				newPS.setString(map.get("description"), "%" + object.getDescription() + "%");
			}
			if (map.get("from") != null) {
				newPS.setString(map.get("from"), "%" + object.getFrom() + "%");
			}
			if (map.get("to") != null) {
				newPS.setString(map.get("to"), "%" + object.getTo() + "%");
			}
			if (map.get("cost") != null) {
				newPS.setString(map.get("cost"), "%" + object.getCost() + "%");
			}
			if (map.get("state") != null) {
				newPS.setString(map.get("state"), object.getState());
			}
			if (map.get("personal_id") != null) {
				PersonDAO pd = new PersonDAO();
				Person driver = pd.read(object.getDriver()).get(0);
				newPS.setInt(map.get("personal_id"), driver.getId()  );
			}
			return newPS;
	}

	@Override
	public void update(Proposal object) {
		try {
			PreparedStatement preparedState = (PreparedStatement) con
					.prepareStatement("UPDATE transportsystem.proposal SET name =?,description=?,proposal.from=?,proposal.to=?,cost=?,state=?,personal_id=?  WHERE id=?");
			preparedState.setString(1, object.getName());
			preparedState.setString(2, object.getDescription());
			preparedState.setString(3, object.getFrom());
			preparedState.setString(4, object.getTo());
			preparedState.setString(5, object.getCost());
			preparedState.setString(6, object.getState());
			if(object.getDriver() == null)
				preparedState.setRef(7, null);/////////////////////////////////////////////////////////answer 
			else
				preparedState.setInt(7, object.getDriver().getId());
			preparedState.setInt(8, object.getId());
			preparedState.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void delete(Proposal object) {
		try {
			PreparedStatement preparedState = doReadPreparedStatement(object,
				"DELETE FROM transportsystem.proposal ");
			preparedState.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

}
